package com.example.springbootdeom.dao;

import com.example.springbootdeom.entity.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TeacherDao {

    //查询个人信息(输入老师id查询)
    @Select("SELECT * FROM `user` WHERE `status`=1 and id=#{id}")
    public Teacher getTeacher(int id);

    //查询所上课班级(传入的值是教师id)
    @Select("SELECT c.class_id id,c.class_name name,c.count FROM teacher_class t LEFT JOIN class c ON t.class_id=c.class_id WHERE t.teacher_id=#{id}")
    public List<Classs> getClasss(int id);

    //查询某一科目班级全部成绩(传入的值是班级号)
        @Select("SELECT u.name,a.result FROM `user` AS u LEFT JOIN achievement AS a ON u.id=a.stu_id WHERE u.status=2 AND u.class_id IN (SELECT class_id FROM `user` WHERE class_id=#{classId}) AND a.cur_id=#{courseId}")
    public List<ClassGrade> getStudentClass(@Param("classId") int classId,@Param("courseId") int courseId);

    //根据课程号班级号来查询课程成绩()
        @Select("SELECT a.cur_id,u.`name` AS StudentName,c.Cname AS curriculumName,a.result,a.stu_id AS stuId,a.cur_id AS curId FROM achievement AS a LEFT JOIN course AS c ON a.cur_id=c.cid LEFT JOIN `user` AS u ON (a.stu_id=u.id) \n" +
                "WHERE a.cur_id=#{rId} AND a.stu_id in (SELECT id FROM `user` WHERE class_id=#{cId})")
    public List<Result> getResult(@Param("rId") int rId,@Param("cId")int cId);

    //修改学生成绩(根据学生id 和 课程id来修改)
    @Update("UPDATE achievement AS a SET  a.result=#{result} WHERE a.stu_id=#{stuId} AND a.cur_id=#{curId}")
    public boolean modifyResult(@Param("result")Double result,@Param("stuId") int stuId,@Param("curId") int curId);

    //查询单个学生信息
    @Select("SELECT * FROM `user` WHERE id=#{id} and `status`=2")
    public Student getStudent(int id);

    //查询老师带有的班级(输入老师的id  返回的是班级的id值 班级名称 还有班级人数)
    @Select("SELECT c.class_id id,c.class_name name,c.count FROM teacher_class t LEFT JOIN class c ON t.class_id=c.class_id WHERE t.teacher_id=#{id}")
    public void getTeacherClass(int id);

    //查询自己的任课课程（输入教师id 返回课程id 课程名 课程时长）
    @Select("SELECT c.Cid,c.Cname FROM course AS c LEFT JOIN `user` AS u ON u.id=c.teacher_id  WHERE u.id=#{id}")
    public List<TeacherCourse> getTeacherCourse(int id);
    //查询班级的学生(传班级id，返回学生集合)
    @Select("SELECT u.id,u.`name`,u.sex,u.age,u.birthday FROM `user` u LEFT JOIN class c ON c.class_id=u.class_id WHERE u.class_id=#{id}")
    public List<Student> getClassAllStudent(int id);

    //查询不及格学生
    @Select("SELECT u.name StudentName,u.id stuId,a.result FROM `user` AS u LEFT JOIN achievement AS a ON u.id=a.stu_id WHERE u.status=2 AND u.class_id IN (SELECT class_id FROM `user` WHERE class_id=#{classId}) AND a.cur_id=#{courseId} AND a.result<60;")
    public List<Result> getFailAllStudent(@Param("classId") int classId,@Param("courseId") int courseId);


}
